﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Gift.DBUtility;//Please add references
namespace Gift.DAL
{
    /// <summary>
    /// 数据访问类:DALArticleType
    /// </summary>
    public partial class DALArticleType
    {
        public DALArticleType()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(Guid id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbArticleType");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Gift.Model.tbArticleType model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbArticleType(");
            strSql.Append("id,parentId,parentIdStr,parentNameStr,depth,rootId,typeName,sortOrder,createTime,keywords,description,isShow)");
            strSql.Append(" values (");
            strSql.Append("@id,@parentId,@parentIdStr,@parentNameStr,@depth,@rootId,@typeName,@sortOrder,@createTime,@keywords,@description,@isShow)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@parentId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@parentIdStr", SqlDbType.NVarChar,500),
					new SqlParameter("@parentNameStr", SqlDbType.NVarChar,500),
					new SqlParameter("@depth", SqlDbType.Int,4),
					new SqlParameter("@rootId", SqlDbType.Int,4),
					new SqlParameter("@typeName", SqlDbType.NVarChar,50),
					new SqlParameter("@sortOrder", SqlDbType.Int,4),
					new SqlParameter("@createTime", SqlDbType.DateTime),
					new SqlParameter("@keywords", SqlDbType.NVarChar,100),
					new SqlParameter("@description", SqlDbType.NVarChar,200),
					new SqlParameter("@isShow", SqlDbType.Bit,1)};
            parameters[0].Value = Guid.NewGuid();
            parameters[1].Value = Guid.NewGuid();
            parameters[2].Value = model.parentIdStr;
            parameters[3].Value = model.parentNameStr;
            parameters[4].Value = model.depth;
            parameters[5].Value = model.rootId;
            parameters[6].Value = model.typeName;
            parameters[7].Value = model.sortOrder;
            parameters[8].Value = model.createTime;
            parameters[9].Value = model.keywords;
            parameters[10].Value = model.description;
            parameters[11].Value = model.isShow;


            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Gift.Model.tbArticleType model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbArticleType set ");
            strSql.Append("parentId=@parentId,");
            strSql.Append("parentIdStr=@parentIdStr,");
            strSql.Append("parentNameStr=@parentNameStr,");
            strSql.Append("depth=@depth,");
            strSql.Append("rootId=@rootId,");
            strSql.Append("typeName=@typeName,");
            strSql.Append("sortOrder=@sortOrder,");
            strSql.Append("createTime=@createTime,");
            strSql.Append("keywords=@keywords,");
            strSql.Append("description=@description,");
            strSql.Append("isShow=@isShow,");
            strSql.Append(" where typeIdentity=@typeIdentity");
            SqlParameter[] parameters = {
					new SqlParameter("@parentId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@parentIdStr", SqlDbType.NVarChar,500),
					new SqlParameter("@parentNameStr", SqlDbType.NVarChar,500),
					new SqlParameter("@depth", SqlDbType.Int,4),
					new SqlParameter("@rootId", SqlDbType.Int,4),
					new SqlParameter("@typeName", SqlDbType.NVarChar,50),
					new SqlParameter("@sortOrder", SqlDbType.Int,4),
					new SqlParameter("@createTime", SqlDbType.DateTime),
					new SqlParameter("@keywords", SqlDbType.NVarChar,100),
					new SqlParameter("@description", SqlDbType.NVarChar,200),
					new SqlParameter("@isShow", SqlDbType.Bit,1),
					new SqlParameter("@id", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@typeIdentity", SqlDbType.Int,4)};
            parameters[0].Value = model.parentId;
            parameters[1].Value = model.parentIdStr;
            parameters[2].Value = model.parentNameStr;
            parameters[3].Value = model.depth;
            parameters[4].Value = model.rootId;
            parameters[5].Value = model.typeName;
            parameters[6].Value = model.sortOrder;
            parameters[7].Value = model.createTime;
            parameters[8].Value = model.keywords;
            parameters[9].Value = model.description;
            parameters[10].Value = model.isShow;

            parameters[11].Value = model.id;
            parameters[12].Value = model.typeIdentity;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int typeIdentity)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbArticleType ");
            strSql.Append(" where typeIdentity=@typeIdentity");
            SqlParameter[] parameters = {
					new SqlParameter("@typeIdentity", SqlDbType.Int,4)
			};
            parameters[0].Value = typeIdentity;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(Guid id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbArticleType ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string typeIdentitylist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbArticleType ");
            strSql.Append(" where typeIdentity in (" + typeIdentitylist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.tbArticleType GetModel(int typeIdentity)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,typeIdentity,parentId,parentIdStr,parentNameStr,depth,rootId,typeName,sortOrder,createTime,keywords,description,isShow from tbArticleType ");
            strSql.Append(" where typeIdentity=@typeIdentity");
            SqlParameter[] parameters = {
					new SqlParameter("@typeIdentity", SqlDbType.Int,4)
			};
            parameters[0].Value = typeIdentity;

            Gift.Model.tbArticleType model = new Gift.Model.tbArticleType();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = new Guid(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["typeIdentity"] != null && ds.Tables[0].Rows[0]["typeIdentity"].ToString() != "")
                {
                    model.typeIdentity = int.Parse(ds.Tables[0].Rows[0]["typeIdentity"].ToString());
                }
                if (ds.Tables[0].Rows[0]["parentId"] != null && ds.Tables[0].Rows[0]["parentId"].ToString() != "")
                {
                    model.parentId = new Guid(ds.Tables[0].Rows[0]["parentId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["parentIdStr"] != null && ds.Tables[0].Rows[0]["parentIdStr"].ToString() != "")
                {
                    model.parentIdStr = ds.Tables[0].Rows[0]["parentIdStr"].ToString();
                }
                if (ds.Tables[0].Rows[0]["parentNameStr"] != null && ds.Tables[0].Rows[0]["parentNameStr"].ToString() != "")
                {
                    model.parentNameStr = ds.Tables[0].Rows[0]["parentNameStr"].ToString();
                }
                if (ds.Tables[0].Rows[0]["depth"] != null && ds.Tables[0].Rows[0]["depth"].ToString() != "")
                {
                    model.depth = int.Parse(ds.Tables[0].Rows[0]["depth"].ToString());
                }
                if (ds.Tables[0].Rows[0]["rootId"] != null && ds.Tables[0].Rows[0]["rootId"].ToString() != "")
                {
                    model.rootId = int.Parse(ds.Tables[0].Rows[0]["rootId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["typeName"] != null && ds.Tables[0].Rows[0]["typeName"].ToString() != "")
                {
                    model.typeName = ds.Tables[0].Rows[0]["typeName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["sortOrder"] != null && ds.Tables[0].Rows[0]["sortOrder"].ToString() != "")
                {
                    model.sortOrder = int.Parse(ds.Tables[0].Rows[0]["sortOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["createTime"] != null && ds.Tables[0].Rows[0]["createTime"].ToString() != "")
                {
                    model.createTime = DateTime.Parse(ds.Tables[0].Rows[0]["createTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["keywords"] != null && ds.Tables[0].Rows[0]["keywords"].ToString() != "")
                {
                    model.keywords = ds.Tables[0].Rows[0]["keywords"].ToString();
                }
                if (ds.Tables[0].Rows[0]["description"] != null && ds.Tables[0].Rows[0]["description"].ToString() != "")
                {
                    model.description = ds.Tables[0].Rows[0]["description"].ToString();
                }
                if (ds.Tables[0].Rows[0]["isShow"] != null && ds.Tables[0].Rows[0]["isShow"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isShow"].ToString() == "1") || (ds.Tables[0].Rows[0]["isShow"].ToString().ToLower() == "true"))
                    {
                        model.isShow = true;
                    }
                    else
                    {
                        model.isShow = false;
                    }
                }
         
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,typeIdentity,parentId,parentIdStr,parentNameStr,depth,rootId,typeName,sortOrder,createTime,keywords,description,isShow ");
            strSql.Append(" FROM tbArticleType ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,typeIdentity,parentId,parentIdStr,parentNameStr,depth,rootId,typeName,sortOrder,createTime,keywords,description,isShow ");
            strSql.Append(" FROM tbArticleType ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbArticleType ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.typeIdentity desc");
            }
            strSql.Append(")AS Row, T.*  from tbArticleType T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataTable GetArticleByTypeIdenity(int typeIdentity)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,typeIdentity,parentId,typeName,sortOrder,keywords,description,isShow from tbarticleType where typeIdentity=@typeIdentity", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@typeIdentity", typeIdentity) };
                table = DbHelperSQL.Query( commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetArticleTypeById(string id)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,typeIdentity,parentId,typeName,sortOrder,keywords,description,isShow from tbarticleType where id=@id", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@id", id) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetArticleTypeByParentTypeAndIsShow(string parentId, int isShow)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,typeIdentity,typeName from tbarticleType where parentId=@parentId and isShow=@isShow order by sortOrder", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@parentId", parentId), new SqlParameter("@isShow", isShow) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public DataTable GetArticleTypeByParentType(string parentId)
        {
            DataTable table = new DataTable();
            try
            {
                string commandText = string.Format("select id,typeIdentity,typeName,depth from tbarticleType where parentId=@parentId ", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@parentId", parentId) };
                table = DbHelperSQL.Query(commandText, commandParameters).Tables[0];
            }
            catch
            {
            }
            return table;
        }

        public string GetArticleTypeByTypeNameAndParentId(string typeName, string parentId)
        {
            string str = "";
            try
            {
                string commandText = string.Format("select id from tbarticleType where typeName=@typeName and parentId=@parentId ", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@typeName", typeName), new SqlParameter("@parentId", parentId) };
                using (SqlDataReader dr = DbHelperSQL.ExecuteReader(commandText, commandParameters))
                {
                    if (dr.Read())
                    {
                        return dr.GetValue(0).ToString();
                    }
                }
            }
            catch
            {
            }
            return str;
        }

        public string GetIdByTypeNameAndParentIdAndIsShow(string typeName, string parentId, int isShow)
        {
            string str = "";
            try
            {
                string commandText = string.Format("select id from tbarticleType where typeName=@typeName and parentId=@parentId and isShow=@isShow", new object[0]);
                SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@typeName", typeName), new SqlParameter("@parentId", parentId),  new SqlParameter("@isShow", isShow) };
                using (SqlDataReader dr = DbHelperSQL.ExecuteReader(commandText, commandParameters))
                {
                    if (dr.Read())
                    {
                        return dr.GetValue(0).ToString();
                    }
                }
            }
            catch
            {
            }
            return str;
        }


        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbArticleType";
            parameters[1].Value = "typeIdentity";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

